unit main;

{$mode objfpc}{$H+}

interface

uses
  Classes, SysUtils, Forms, Controls, Graphics, Dialogs, StdCtrls, StrUtils, ValEdit,
  Grids, fpspreadsheet, fpsallformats, fpsTypes, fgl;

type
  TIDDict = specialize TFPGMap<string, string>;

  { TMainForm }

  TMainForm = class(TForm)
    SheetNameBox: TComboBox;
    Label6: TLabel;
    showMemoButton: TButton;
    BrowseStudentFileButton: TButton;
    AddFileButton: TButton;
    DeleteFileButton: TButton;
    saveResultButton: TButton;
    FindIDNumberButton: TButton;
    FilterStudentButton: TButton;
    Label7: TLabel;
    Label8: TLabel;
    Label2: TLabel;
    Label3: TLabel;
    Label4: TLabel;
    Label5: TLabel;
    FileListGrid: TStringGrid;
    ResultGrid: TStringGrid;
    StaticText1: TStaticText;
    StudentFileNameEdit: TEdit;
    Label1: TLabel;
    OpenDialog: TOpenDialog;
    StudentNameEdit: TEdit;
    IDNameEdit: TEdit;
    procedure AddFileButtonClick(Sender: TObject);
    procedure BrowseStudentFileButtonClick(Sender: TObject);
    procedure DeleteFileButtonClick(Sender: TObject);
    procedure saveResultButtonClick(Sender: TObject);
    procedure FilterStudentButtonClick(Sender: TObject);
    procedure FindIDNumberButtonClick(Sender: TObject);
    procedure FormClick(Sender: TObject);
    procedure FormCreate(Sender: TObject);
    procedure ProcessExcelFile(FileName: string; i: integer);
    procedure showMemoButtonClick(Sender: TObject);
    procedure StudentFileNameEditChange(Sender: TObject);
  private
    FileList: TStringList;
    IDDict: TIDDict;
  public

  end;

var
  MainForm: TMainForm;

implementation

{$R *.lfm}
uses log;

{ TMainForm }

function ExtractNumbersAndX(S: string): string;
var
  ResultStr: string;
  i: Integer;
begin
  ResultStr := '';
  for i := 1 to Length(S) do
  begin
    if (S[i] in ['0'..'9']) or (S[i] = 'x') or (S[i] = 'X') then
      ResultStr := ResultStr + S[i];
  end;
  Result := ResultStr;
end;

function GetCheckDigit(const ID: string): Char;
const
  Weights: array[0..16] of Integer = (7, 9, 10, 5, 8, 4, 2, 1, 6, 3, 7, 9, 10, 5, 8, 4, 2);
  CheckCodes: array[0..10] of Char = ('1', '0', 'X', '9', '8', '7', '6', '5', '4', '3', '2');
var
  Sum: Integer;
  I: Integer;
begin
  Sum := 0;
  for I := 1 to 17 do
    Sum := Sum + (StrToInt(ID[I]) * Weights[I - 1]);
  Result := CheckCodes[Sum mod 11];
end;

function ExcelColumnNameToInt(ColName: string): integer;
var
  c: char;
begin
  result := 0;
  ColName := UpperCase(ColName);
  for c in ColName do
  begin
    result := result * 10 + ord(c) - ord('A');
  end;
end;


function IntToExcelColumnName(index: Integer): string;
var
  t: integer;
begin
  result := '';
  if (index < 0) or (index >= 16384)  then
     exit;

  index := index + 1;
  while index > 0 do
  begin
    t := index mod 26;
    index := index div 26;
    result := Chr(Ord('A') + t - 1) + result;
  end;
end;

function IsValidDate(const DateStr: string): Boolean;
var
  Year, Month, Day: Integer;
begin
  if Length(DateStr) <> 8 then
    Exit(False);

  Year := StrToIntDef(Copy(DateStr, 1, 4), 0);
  Month := StrToIntDef(Copy(DateStr, 5, 2), 0);
  Day := StrToIntDef(Copy(DateStr, 7, 2), 0);

  // Check if the year is a valid year (e.g., 1900-2100)
  if (Year < 1900) or (Year > 2100) then
    Exit(False);

  // Check if the month is between 1 and 12, and Day < 1
  if (Month < 1) or (Month > 12) or (Day < 1) then
    Exit(False);

  // Check if the day is within the valid range for the given month and year
  case Month of
    1, 3, 5, 7, 8, 10, 12:
      if (Day > 31) then
        Exit(False);
    4, 6, 9, 11:
      if (Day > 30) then
        Exit(False);
    2:
      begin
        // Leap year check
        if (Year mod 4 = 0) and ((Year mod 100 <> 0) or (Year mod 400 = 0)) then
        begin
          if (Day > 29) then
            Exit(False)
        end
        else
          if (Day > 28) then
            Exit(False);
      end;
  end;

  Result := True;
end;

function IsValidIDNumber(const S: string): Boolean;
var
  i: integer;
  dateString: String;
begin
  Result := Length(S) = 18;
  if not Result then Exit;

  // 检查是否全部是数字
  Result := True;
  for i := 1 to 17 do
  begin
    if not CharInSet(S[i], ['0'..'9']) then
    begin
      Result := False;
      Exit;
    end;
  end;

  if not CharInSet(S[18], ['0'..'9', 'x', 'X']) then
  begin
    Result := False;
    Exit;
  end;
  dateString := Copy(S, 7, 8);
  if not IsValidDate(dateString) then
  begin
    Result := False;
    Exit;
  end;
{  result := GetCheckDigit(S) = S[18]; }
end;

procedure TMainForm.ProcessExcelFile(FileName: string; i: integer);
var
  DataSheet: TsWorkSheet;
  Workbook: TSWorkbook;
  cell: PCell;
  CellValue: string;
  k, v: string;
  extName: String;
  sheetName: string;
  t: integer;
begin
  try
    // 打开 Excel 文件
    Workbook := TSWorkbook.Create;
    memoForm.Memo.lines.add('正在处理 ' + FileName);
    extName := ExtractFileExt(FileName);
    try
      if extName = '.xls' then
        Workbook.ReadFromFile(FileName, sfExcel8)
      else
        Workbook.ReadFromFile(FileName, sfOOXML);

      // 获取第一个工作表
      DataSheet := Workbook.GetFirstWorksheet;
      SheetName := DataSheet.Name;
      memoForm.Memo.lines.add('  工作表名 ' + SheetName);
      t := 0;
      // 遍历所有单元格
      for cell in DataSheet.Cells do
      begin
        CellValue := Trim(DataSheet.ReadAsText(cell));
        CellValue := ExtractNumbersAndX(Uppercase(CellValue));
        if CellValue.Length >= 18 then
        begin
          k := leftStr(CellValue, 18);
          if IsValidIDNumber(k) then
          begin
            // 将身份证号加入字典
            // memoForm.Memo.lines.add('加入身份证号 ' + CellValue);
            v := FileName + '$' +  IntToExcelColumnName(cell^.Col) + (cell^.Row+1).ToString + ';';
            if IDDict.IndexOf(k) = -1 then
            begin
              IDDict.KeyData[k] := v;
              inc(t);
              FileListGrid.Rows[i][2] := t.ToString;
            end
            else
              IDDict.KeyData[k] := IDDict.KeyData[k] + v;
          end else
          begin
            memoForm.Memo.lines.add('不符合规则的身份证号 ' + CellValue);
          end;
        end;
      end;

    finally
      Workbook.Free;
    end;
  except
    on E: Exception do
    begin
      ShowMessage('发生错误: ' + E.Message);
    end;
  end;

end;

procedure TMainForm.showMemoButtonClick(Sender: TObject);
begin
  if not memoForm.Visible then
    memoForm.show;
end;

procedure TMainForm.StudentFileNameEditChange(Sender: TObject);
var
  i, c: integer;
  MyWorkBook: TsWorkbook;
  SheetName: string;
begin
  MyWorkbook := TsWorkbook.Create;
  try
    // 打开Excel文件
    MyWorkbook.ReadFromFile(StudentFileNameEdit.Text);

    // 遍历所有工作表并获取名称
    c := MyWorkbook.GetWorksheetCount;
    SheetNameBox.Items.Clear;
    for i := 0 to c - 1 do
    begin
      SheetName := MyWorkbook.GetWorksheetByIndex(i).Name;
      SheetNameBox.Items.Add(SheetName);
    end;
  finally
    // 释放工作簿对象
    MyWorkbook.Free;
  end;
end;

procedure TMainForm.BrowseStudentFileButtonClick(Sender: TObject);
begin
  OpenDialog.Options :=  OpenDialog.Options + [ofFileMustExist];
  if OpenDialog.execute then
  begin
    StudentFileNameEdit.Text := OpenDialog.FileName;
  end;
end;

procedure TMainForm.DeleteFileButtonClick(Sender: TObject);
var
  c, r, i: integer;
  FileName: string;
begin
  c := FileListGrid.RowCount;
  r := FileListGrid.Row;
  FileName := FileListGrid.Rows[r][1];

  if r < c - 1 then
    for i := r to c - 2 do
      FileListGrid.Rows[i][1] := FileListGrid.Rows[i+1][1];

  FileListGrid.RowCount := c - 1;
  i := FileList.IndexOf(FileName);
  if i <> -1 then
    FileList.Delete(i);
end;

procedure TMainForm.saveResultButtonClick(Sender: TObject);
var
  ResultWorkbook: TsWorkbook;
  ResultWorksheet: TsWorksheet;
  AFileName: string;
  currentDate: TDateTime;
  formattedDate: string;
  i: integer;
begin
  // 获取当前日期和时间
  currentDate := now;
  // 格式化日期时间为 YYYYMMDDHHMMSS
  formattedDate := FormatDateTime('yyyyMMddhhnnss', currentDate);
  AFileName := ParamStr(0);
  AFileName := Copy(AFileName, 1, RPos('\', AFileName));
  // 生成文件名
  AFileName := AFileName + '过滤数据' + formattedDate + '.xlsx';

  // 创建一个新的工作簿对象
  ResultWorkbook := TsWorkbook.Create;
  try
    // 获取默认的工作表（通常是第一个工作表）
    ResultWorksheet := ResultWorkbook.AddWorksheet('过滤结果', true);
    ResultWorksheet.WriteCellValueAsString(0, 0, '序号');
    ResultWorksheet.WriteCellValueAsString(0, 1, '姓名');
    ResultWorksheet.WriteCellValueAsString(0, 2, '身份证号');
    ResultWorksheet.WriteCellValueAsString(0, 3, '过滤数据来源');

    if ResultGrid.RowCount >= 2 then
      for i := 1 to ResultGrid.RowCount - 1 do
      begin
        ResultWorksheet.WriteCellValueAsString(i, 0, resultGrid.Cells[0, i]);
        ResultWorksheet.WriteCellValueAsString(i, 1, resultGrid.Cells[1, i]);
        ResultWorksheet.WriteCellValueAsString(i, 2, '''' + resultGrid.Cells[2, i]);
        ResultWorksheet.WriteCellValueAsString(i, 3, resultGrid.Cells[3, i]);
      end;
    // 保存工作簿到文件
    ResultWorkbook.WriteToFile(AFileName, True); // True表示强制覆盖写入
  finally
    // 释放工作簿对象
    ResultWorkbook.Free;
  end;
  showMessage('过滤结果已经保存到文件 ' + AFileName);
end;

procedure TMainForm.FilterStudentButtonClick(Sender: TObject);
var
  DataSheet: TsWorkSheet;
  Workbook: TSWorkbook;
  IDCell, NameCell: PCell;
  FileName, extName: string;
  sheetName: string;
  i, endRow, resultRow: integer;
  NameIndex, IDIndex, resultIndex: integer;
  IDCellValue, NameCellValue: String;
begin
  FileName := StudentFileNameEdit.text;
  if FileName = '' then
  begin
    ShowMessage('没有选择需要过滤的学生数据表');
    exit;
  end;
  memoForm.Show;
  NameIndex := ExcelColumnNameToInt(StudentNameEdit.text);
  IDIndex := ExcelColumnNameToInt(IDNameEdit.text);
  try
    // 打开 Excel 文件
    Workbook := TSWorkbook.Create;
    // memoForm.Memo.lines.add('正在处理 ' + FileName);
    extName := ExtractFileExt(FileName);
    try
      if extName = '.xls' then
        Workbook.ReadFromFile(FileName, sfExcel8)
      else
        Workbook.ReadFromFile(FileName, sfOOXML);

      // 获取选定工作表
      DataSheet := Workbook.GetWorksheetByName(SheetNameBox.Text);
      SheetName := DataSheet.Name;
      // 遍历所有单元格
      endRow := DataSheet.GetLastRowIndex(true);
      resultIndex := 1;
      for i := 0 to endRow do
      begin
        IDCell := DataSheet.GetCell(i, IDIndex);
        IDCellValue := DataSheet.ReadAsText(IDCell);
        // memoForm.Memo.lines.add('获取值 ' + IDCellValue);
        if IDDict.IndexOf(IDCellValue) <> -1 then
        begin
          NameCell := DataSheet.GetCell(i, NameIndex);
          NameCellValue := DataSheet.ReadAsText(NameCell);
          ResultRow := ResultGrid.RowCount;
          ResultGrid.RowCount := ResultRow + 1;
          ResultGrid.Cells[0, ResultRow] := ResultIndex.ToString;
          ResultGrid.Cells[1, ResultRow] := NameCellValue;
          ResultGrid.Cells[2, ResultRow] := IDCellValue;
          ResultGrid.Cells[3, ResultRow] := IDDict.KeyData[IDCellValue];
          inc(ResultIndex);
        end;
      end;

    finally
      Workbook.Free;
    end;
  except
    on E: Exception do
    begin
      ShowMessage('发生错误: ' + E.Message);
    end;
  end;
  if ResultGrid.RowCount >= 2 then
     saveResultButton.Enabled := True;
  showMessage('过滤学生数据完成');
end;

procedure TMainForm.FindIDNumberButtonClick(Sender: TObject);
var
  c, i: integer;
  FileName: string;
begin
  memoForm.Show;
  memoForm.memo.lines.add('以下分析所有长度大于等于18位的字符串并记录符合身份证号编码规则的字符串');
  memoForm.memo.lines.add('(大于18位部分被截断)，由于可能存在校验失败的身份证号，所以不对身份证是');
  memoForm.memo.lines.add('否校验正确进行测试，仅测试出生日期的合理性，并输出不符合日期规则的字符');
  memoForm.memo.lines.add('以供手工核验');
  memoForm.memo.lines.add('');
  c := FileListGrid.RowCount;
  if c >= 2 then
    for i := 1 to c - 1 do
    begin
      FileName := FileListGrid.Rows[i][1];
      processExcelFile(FileName, i);
    end;
  for i := 0 to IDDict.Count - 1 do
  begin
  //  memoForm.memo.lines.add((i+1).ToString + ' ' + IDDict.Keys[i] + ':' + IDDict.Data[i]);
  end;
  memoForm.memo.lines.add('---------------------------------------');
  memoForm.memo.lines.add('以上输出不符合规则的身份证号，可能是原始表输入错误，请排出错误后手工核对');
  memoForm.memo.lines.add('');
  memoForm.memo.lines.add('获取所有身份证号完成');
  memoForm.memo.lines.add('');
  showMessage('获取所有身份证号完成');
end;

procedure TMainForm.FormClick(Sender: TObject);
begin
end;

procedure TMainForm.AddFileButtonClick(Sender: TObject);
var
  FileName: string;
  c: integer;
begin
  if OpenDialog.execute then
  begin
    FileName := OpenDialog.FileName;
    if FileList.IndexOf(FileName) = -1 then
    begin
      FileList.Add(FileName);
      c := FileListGrid.RowCount;
      FileListGrid.RowCount := c + 1;
      c := FileListGrid.RowCount - 1;
      FileListGrid.Rows[c][0] := c.toString;
      FileListGrid.Rows[c][1] := FileName;

    end;
  end;
end;

procedure TMainForm.FormCreate(Sender: TObject);
begin
  FileList := TStringList.Create;
  IDDict := TIDDict.Create;
end;

end.

